﻿using AntDesign;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Collections.Generic;
using System.Reflection;

namespace WareHouseManagement.Commom
{
    public class NPOIService
    {
        #region 将泛型集合中的实体导出到指定的Excel文件

        /// <summary>
        /// 将泛型集合中的实体导出到指定的Excel文件
        /// </summary>
        /// <typeparam name="T">泛型方法中的类型</typeparam>
        /// <param name="fileName">Excel路径和文件名</param>
        /// <param name="dataList">包含若干对象的泛型集合</param>
        /// <param name="columnNames">实体列的中文标题集合</param>
        /// <param name="version">Excel的版本号，规定0:2007以前版本，否则为2007及以上版本</param>
        /// <returns>成功返回ture</returns>
        public static byte[] ExportToExcel<T>(List<T> dataList,
        Dictionary<string, string> columnNames, int version = 0) where T : class
        {
            //【1】基于NPOI创建工作簿和工作表对象
            HSSFWorkbook hssf = new HSSFWorkbook();   //2007以下版本
            XSSFWorkbook xssf = new XSSFWorkbook();   //2007以上版本
            //根据不同的office版本创建不同的工作簿对象
            IWorkbook workBook = null;
            if (version == 0)
                workBook = hssf;
            else
                workBook = xssf;
            //【2】创建工作表
            ISheet sheet = workBook.CreateSheet("sheet1");
            Type type = typeof(T);
            PropertyInfo[] propertyinfos = type.GetProperties();//获取类型的公共属性
            //【3】循环生成列标题和设置样式
            IRow rowTitle = sheet.CreateRow(0);
            int k = 1;
            try
            {
                for (int i = 0; i < propertyinfos.Length; i++)
                {
                    if (columnNames.ContainsKey(propertyinfos[i].Name))
                    {
                        ICell cell = rowTitle.CreateCell(k);   //创建单元格  propertyinfos[i].Name
                        cell.SetCellValue(columnNames[propertyinfos[i].Name]);// 设置行标题
                        SetCellStyle(workBook, cell);                                                 //设置单元格边框 
                        SetColumnWidth(sheet, k, 20);
                        k++;
                    }
                    //设置列宽   
                }
                //【4】循环实体集合生成各行数据
                for (int i = 0; i < dataList.Count; i++)
                {
                    IRow row = sheet.CreateRow(i + 1);
                    int l = 1;
                    for (int j = 0; j < propertyinfos.Length; j++)
                    {
                        ICell cell2 = row.CreateCell(0);
                        cell2.SetCellValue(i + 1);  //赋值
                        SetCellStyle(workBook, cell2);
                        if (columnNames.ContainsKey(propertyinfos[j].Name))
                        {
                            ICell cell = row.CreateCell(l);
                            T model = dataList[i];    //根据泛型找到具体化的实体对象
                            object obj = propertyinfos[j].GetValue(model, null);//基于反射获取实体属性值
                            if (obj == null)
                            {
                                cell.SetCellValue("0");  //赋值
                                SetCellStyle(workBook, cell);
                            }
                            else
                            {
                                cell.SetCellValue(obj.ToString());  //赋值
                                SetCellStyle(workBook, cell);
                            }


                            l++;
                        }

                    }
                }
                using (var memoryStream = new MemoryStream())
                {
                    workBook.Write(memoryStream);
                    return memoryStream.ToArray();
                }
            }
            catch (Exception e)
            {

                var a = 1;
                return null;
            }
            //    using (FileStream fs = File.OpenWrite(dialog.FileName))
            //    {
            //        workBook.Write(fs, true);
            //        return true;
            //    }
            //【5】保存为Excel文件
            ////System.Windows.Forms.SaveFileDialog dialog = new System.Windows.Forms.SaveFileDialog();
            ////if (version == 0)
            ////{
            ////    dialog.Filter = "Exel Files|*.xls";
            ////}
            ////else
            ////{
            ////    dialog.Filter = "Exel Files|*.xlsx";
            ////}
            ////dialog.FileName = DateTime.Now.ToString("yyyyMMddHHmmss");
            ////if (dialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            ////{
            ////    using (FileStream fs = File.OpenWrite(dialog.FileName))
            ////    {
            ////        workBook.Write(fs, true);
            ////        return true;
            ////    }
            ////}
            ////else
            ////{
            ////    return false;
            ////}
            
        }
        #region 辅助方法

        /// <summary>
        /// 设置cell单元格边框的公共方法
        /// </summary>
        /// <param name="workBook">接口类型的工作簿，能适应不同版本</param>
        /// <param name="cell">cell单元格对象</param>
        private static void SetCellStyle(IWorkbook workBook, ICell cell)
        {
            ICellStyle style = workBook.CreateCellStyle();
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.BorderTop = BorderStyle.Thin;
            style.Alignment = HorizontalAlignment.Center;//水平对齐
            style.VerticalAlignment = VerticalAlignment.Center;//垂直对齐
            //设置字体
            IFont font = workBook.CreateFont();
            font.FontName = "微软雅黑";
            font.FontHeight = 15 * 15;
            font.Color = IndexedColors.Green.Index;   //字体颜色         
            //font.Color =(short )FontColor .Red  ;
            style.SetFont(font);
            cell.CellStyle = style;
        }
        /// <summary>
        /// 设置cell单元格列宽的公共方法
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="index">第几列</param>
        /// <param name="width">具体宽度值</param>
        private static void SetColumnWidth(ISheet sheet, int index, int width)
        {
            sheet.SetColumnWidth(index, width * 256);
        }
        #endregion
        #endregion
    }
}
